package server;

import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.mysql.jdbc.Connection;


public class Database {
	
	final static String SQLHOSTNAME = "localhost";
	final static String SQLPORT = "3306";
	final static String SQLDBNAME = "test";	 
	final static String SQLUSER = "root";
	final static String SQLPASSWORD = "";
	private static Connection sqlConnection = null;

	/**
	 * Initiate the driver for the SQL connection
	 */
	public static void initDatabase(){
		//open the driver for the SQL connection
		try {
			Class.forName("org.gjt.mm.mysql.Driver").newInstance();
		} catch (Exception e) {
			System.err.println("Unable to load driver.");
			e.printStackTrace();
		}
	}
	
	/**
	 * Reads the DataSets from a specified table
	 * @param table the SQL table were the DataSets are stored
	 * @return ArrayList which contains the DataSets
	 */
	public static ArrayList<DataSet> readDataSetsFromDB(String table){
		ArrayList<DataSet> dataSet=new ArrayList<DataSet>();
		try {
			//open the connection to read from the specified table
			String url = "jdbc:mysql://" + SQLHOSTNAME + ":" + SQLPORT + "/" + SQLDBNAME;
			sqlConnection = (Connection) DriverManager.getConnection(url, SQLUSER, SQLPASSWORD);
			Statement sqlStatement = sqlConnection.createStatement();
			String query = "select * from "+table;
			java.sql.ResultSet rs = sqlStatement.executeQuery(query);

			//Separate all contents into the dataSet ArrayList
			while (rs.next()) {
				DataSet set=new DataSet();	
				set.setSpotId(rs.getString("spotId"));
				set.setNewpacket(Boolean.parseBoolean(rs.getString("NewPacket")));
				set.setSeqNr(Integer.parseInt(rs.getString("SeqNr")));
				set.setRssi(Integer.parseInt(rs.getString("RSSI")));
				set.setLQI(Integer.parseInt(rs.getString("LQI")));
				set.setTime(rs.getString("Time"));
				set.setSendingTime(rs.getString("Sendingtime"));		
				dataSet.add(set);
			}
			//close the SQL connection
			sqlStatement.close();
			sqlConnection.close();
			
		} catch (SQLException sqle) {
			System.out.println("SQLException: " + sqle.getMessage());
			System.out.println("SQLState: " + sqle.getSQLState());
			System.out.println("VendorError: " + sqle.getErrorCode());
			sqle.printStackTrace();
		}
		return dataSet;
	}
	
	/**
	 * Writes the DataSets to a specified SQL table
	 * @param table the SQL table were the DataSets should be stored
	 * @param set the ArrayList containing all DataSets which should be written to the SQL table
	 */
	public static void writeDataSetsToDB(String table,ArrayList<DataSet> set){
		try {	
			//open the connection to write in the specified table
			String url = "jdbc:mysql://" + SQLHOSTNAME + ":" + SQLPORT + "/" + SQLDBNAME;
			sqlConnection = (Connection) DriverManager.getConnection(url, SQLUSER, SQLPASSWORD);
			Statement sqlStatement = sqlConnection.createStatement();
			
			//if the table exist delete it first
            try {
                sqlStatement.executeUpdate("DROP TABLE " + table);
            } catch (Exception e) {
            }	
            
            //create the new table
            sqlStatement.executeUpdate(
                    "CREATE TABLE " + table + " (spotId char(20), "
                    + "SeqNr char(20),RSSI char(20), LQI char(20), Time varchar(40), Sendingtime varchar(40),NewPacket char(20))");
			
            //write all DataSets into the specified table
            for (DataSet d : set){
				sqlStatement.executeUpdate("INSERT INTO " + table
	                     + "(spotId,seqNr,RSSI,LQI,Time,SendingTime,NewPacket)"
	                     + " VALUES(\'" + d.getSpotId() + "\',\'" + d.getSeqNr() + "\',\'" + d.getRssi()  + "\',\'" + d.getLQI() + "\',\'" + d.getTime() + "\',\'" + d.getSendingTime() + "\',\'" + d.isNewpacket()  +  "\')");
			}
            
            //close the SQL connection
			sqlStatement.close();
			sqlConnection.close();
			
		} catch (SQLException sqle) {
			System.out.println("SQLException: " + sqle.getMessage());
			System.out.println("SQLState: " + sqle.getSQLState());
			System.out.println("VendorError: " + sqle.getErrorCode());
			sqle.printStackTrace();
		}
	}
	
	/**
	 * Writes the ReferenzPoints to a specified SQL table
	 * @param table the SQL table were the ReferenzPoints should be stored
	 * @param refPoints the ArrayList containing all ReferentzPoints which should be written to the SQL table
	 */
	public static void writeReferenzPointsToDB(String table,ArrayList<ReferencePoint> refPoints){
		try {
			//open the connection to write in the specified table
			String url = "jdbc:mysql://" + SQLHOSTNAME + ":" + SQLPORT + "/" + SQLDBNAME;
			sqlConnection = (Connection) DriverManager.getConnection(url, SQLUSER, SQLPASSWORD);
			Statement sqlStatement = sqlConnection.createStatement();
			
			//if the table exist delete it first
	        try {
	            sqlStatement.executeUpdate("DROP TABLE " + table);
	        } catch (Exception e) {
	        }	

	        //create a new table for each ReferentzPoint
	        sqlStatement.executeUpdate( "CREATE TABLE " + table + " (PosId char(20), "+ "X char(20),Y char(20), TableName char(200))");
	        for(ReferencePoint r:  refPoints){
	        	String tablenames="";
	        	// each TableName is separated with a $
	        	for(SinglePoint s:r.singlePoints){
	        		tablenames+=s.tablename+"$";
	        	}
				sqlStatement.executeUpdate("INSERT INTO " + table
	                     + "(PosId,X,Y,TableName)"
	                     + " VALUES(\'" +r.id + "\',\'" + r.X + "\',\'" + r.Y  + "\',\'" + tablenames + "\')");	       
	        }
	        
	        //close the SQL connection
			sqlStatement.close();
			sqlConnection.close();	
	        
	        
		} catch (SQLException sqle) {
			System.out.println("SQLException: " + sqle.getMessage());
			System.out.println("SQLState: " + sqle.getSQLState());
			System.out.println("VendorError: " + sqle.getErrorCode());
			sqle.printStackTrace();
		}		
	}
	
	/**
	 * Reads the ReferenzPoints from a specified SQL table
	 * @param table the SQL table were the ReferenzPoints are stored
	 * @return ArrayList which contains the ReferenzPoints
	 */
	public static ArrayList<ReferencePoint> readReferenzPointsFromDB(String table){
		ArrayList<ReferencePoint> refPoints=new ArrayList<ReferencePoint>();
		try {
			//open the connection to read from the specified table
			String url = "jdbc:mysql://" + SQLHOSTNAME + ":" + SQLPORT + "/" + SQLDBNAME;
			sqlConnection = (Connection) DriverManager.getConnection(url, SQLUSER, SQLPASSWORD);
			Statement sqlStatement = sqlConnection.createStatement();
			String query = "select * from "+table;
			java.sql.ResultSet rs = sqlStatement.executeQuery(query);
			
			//Separate all contents into the refPoints ArrayList, each TableName is separated with a $
			while (rs.next()) {		
				String tables[]=rs.getString("TableName").split("[$]");
				int x=Integer.parseInt(rs.getString("X"));
				int y=Integer.parseInt(rs.getString("Y"));
				String id=rs.getString("PosId");
				ReferencePoint referenzPoint=new ReferencePoint(x, y, id);
				for(String t:tables){
					referenzPoint.addSinglePoint(t);
				}
				refPoints.add(referenzPoint);
			}
			
			//close the SQL connection
			sqlStatement.close();
			sqlConnection.close();
			
		} catch (SQLException sqle) {
			System.out.println("SQLException: " + sqle.getMessage());
			System.out.println("SQLState: " + sqle.getSQLState());
			System.out.println("VendorError: " + sqle.getErrorCode());
			sqle.printStackTrace();
		}
		return refPoints;
	}

	
}
